﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cExcel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using Ext.Net;
using ObjectLayer;
using System.Collections;
using System.Web;

namespace AccessLayer
{
    public class RevenuesExcelReader
    {
        cExcel.Application app;
        cExcel.Workbook book;
        cExcel.Worksheet sheet;
        cExcel.Range rng;
        object valueMissing = System.Reflection.Missing.Value;
        int beginRow = 24;
        int capRow = 8;
        int timeRow = 10;
        
        Hashtable hashCost=new Hashtable();
        
        public void GetCode()
        {
            if(Global.GlobalVariable.HashDimCostRevenue==null)
            {
                Global.GlobalVariable.HashDimCostRevenue = new Hashtable();
                List<dim_costs_revenuesBO> lst = dim_costs_revenuesDAO.GetAllC();
                foreach (dim_costs_revenuesBO item in lst)
                {
                    string code = item.code2;// +"-" + item.unit_code;
                    if (item.code2 != "")
                    {
                        if (Global.GlobalVariable.HashDimCostRevenue[code] == null)
                        {
                            Global.GlobalVariable.HashDimCostRevenue.Add(code, item);
                        }
                    }
                    else
                    {
                        if (Global.GlobalVariable.HashDimCostRevenue[item.slug] == null)
                        {
                            Global.GlobalVariable.HashDimCostRevenue.Add(item.slug, item);
                        }
                    }
                }
            }
        }
        public void GetUnit()
        {
            if (Global.GlobalVariable.HashUnit == null)
            {
                Global.GlobalVariable.HashUnit = new Hashtable();
                List<dim_unitBO> lst = dim_unitDAO.GetAllC();
                foreach (dim_unitBO item in lst)
                {
                    if (Global.GlobalVariable.UnitIndentity < int.Parse(item.code))
                        Global.GlobalVariable.UnitIndentity = int.Parse(item.code) + 1;
                    if (Global.GlobalVariable.HashUnit[item.slug] == null)
                    {
                        Global.GlobalVariable.HashUnit.Add(item.slug, item);
                    }
                }
            }
        }
        public void GetCost(int timeKey,string branch_code)
        {
            List<fact_costs_revenuesBO> lst = fact_costs_revenuesDAO.GetByTimeKey(timeKey);
            foreach (fact_costs_revenuesBO item in lst)
            {
                string key=item.timekey+"-"+item.branch_code+"-"+item.cost_code+"-"+item.unit_code;
                if (hashCost[key] == null)
                {
                    hashCost.Add(key, item);
                }
            }
        }
        public void Import(string path, DirectEventArgs e)
        {
            try
            {
                GetUnit();
                GetCode();
                
                app = new cExcel.Application();
                book = app.Workbooks.Open(path, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing);
                sheet = (cExcel.Worksheet)book.Sheets[1];
                try
                {
                    rng = sheet.get_Range("B" + capRow, "B" + capRow);
                    if (!Lib.StringProccess.RemoveUnicode(rng.Value2.ToString().ToLower()).Replace(" ", "").Contains("doanhthu"))
                    {
                        e.Success = false;
                        e.ErrorMessage = "File không đúng định dạng!";
                        return;
                    }
                    int timekey = 0;
                    rng = sheet.get_Range("B" + timeRow, "B" + timeRow);
                    string[] arrTime = rng.Value2.ToString().Split(' ');
                    //string[] arr = arrTime[arrTime.Length - 1].Split('/');
                    //Array.Reverse(arr);
                    string time = arrTime[5] + arrTime[1];
                    string slug = "";
                    GetCost(int.Parse(time),"");
                    int i=beginRow-1;
                    do 
                    {
                        string code = "";
                        i++;
                        rng = sheet.get_Range("G" + i, "G" + i);
                        if (rng.Value2 == null || rng.Value2.ToString() == "")
                        {
                            break;
                        }
                        
                        string name = "";
                        string unitSlug = "";
                        string unitName="";
                        dim_unitBO objUnit = null;
                        code = rng.Value2.ToString();
                        rng = sheet.get_Range("B" + i, "B" + i);
                        if (rng.Value2!=null)
                            name = rng.Value2.ToString();
                        slug = Global.GlobalMethod.CreateSlug(name);
                        rng=sheet.get_Range("F"+i,"F"+i);
                        if(rng.Value2!=null)
                            unitName=rng.Value2.ToString();
                        if(rng.Value2!=null)
                            unitSlug = Global.GlobalMethod.CreateSlug(unitName).ToLower();

                        if(name=="" && unitSlug=="")
                            continue;

                        rng=sheet.get_Range("G"+i,"G"+i);
                        // = rng.Value2==null?"":rng.Value2.ToString();
                        //if (code == "") break;

                        rng = sheet.get_Range("Q" + i, "Q" + i);
                        double cost=0;
                        if (rng.Value2 != null && rng.Value2.ToString() != "")
                        {
                            cost = double.Parse(rng.Value2.ToString());
                        }
                        
                        

                        string revCode = code;
                        dim_costs_revenuesBO objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenue[revCode];
                        if (objDim == null)
                        {
                            objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenue[slug];
                        }
                        //them tieu chi moi
                        if (objDim == null)
                        {
                            
                            objUnit = (dim_unitBO)Global.GlobalVariable.HashUnit[unitSlug];
                            // Them dom vi moi
                            if (objUnit == null)
                            {
                                objUnit = new dim_unitBO();
                                objUnit.code = Lib.CreateCode.CreateCode3("", Global.GlobalVariable.UnitIndentity.ToString());
                                objUnit.name = unitName;
                                objUnit.slug = unitSlug;
                                if (dim_unitDAO.InsertUpdateDelete(objUnit, ProviderAction.Insert) > 0)
                                    Global.GlobalVariable.HashUnit.Add(objUnit.slug, objUnit);
                            }
                            objDim = new dim_costs_revenuesBO();
                            objDim.name = name;
                            objDim.unit_code = objUnit.code;
                            objDim.slug = Global.GlobalMethod.CreateSlug(name);
                            objDim.code = code;
                            objDim.code2 = code;
                            if (dim_costs_revenuesDAO.InsertUpdateDelete(objDim, ProviderAction.Insert) > 0)
                            {
                                Global.GlobalVariable.HashDimCostRevenue.Add(objDim.code2, objDim);

                            }

                        }
                        fact_costs_revenuesBO obj = new fact_costs_revenuesBO();
                        obj.cost_code = objDim.code;
                        obj.unit_code = objDim.unit_code;
                        obj.timekey = int.Parse(time);
                        obj.cost = cost;
                        string key=time+"-"+""+"-"+objDim.code+"-"+objDim.unit_code;
                        if(hashCost[key]==null)
                            fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Insert);
                        else
                            fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Update);
                        
                    } while (true);
                    

                }
                catch (System.Exception ex)
                {
                    //X.Msg.Show(new MessageBoxConfig
                    // {
                    //     Buttons = MessageBox.Button.OK,
                    //     Icon = MessageBox.Icon.ERROR,
                    //     Title = "Fail",
                    //     Message = "Có lỗi sảy ra trong quá trình xử lý"
                    // });
                    Lib.Log.WriteLog("", ex.ToString());
                    e.Success = false;
                    e.ErrorMessage = "";
                }
                finally
                {
                    book.Close(false, valueMissing, valueMissing);
                    app.Quit();
                    File.Delete(path);
                }
            }
            catch (Exception ex)
            {
                Lib.Log.WriteLog("",ex.ToString());
                e.Success = false;
                e.ErrorMessage = "Lỗi hệ thống!\n" + ex.ToString();
            }
        }
        public void Optimize(string path, DirectEventArgs e)
        {
            try
            {
                GetCode();
                app = new cExcel.Application();
                book = app.Workbooks.Open(path, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing, valueMissing, valueMissing
                    , valueMissing, valueMissing, valueMissing, valueMissing);
                sheet = (cExcel.Worksheet)book.Sheets[1];
                try
                {
                    rng = sheet.get_Range("B" + capRow, "B" + capRow);
                    if (!Lib.StringProccess.RemoveUnicode(rng.Value2.ToString().ToLower()).Replace(" ", "").Contains("chiphi"))
                    {
                        e.Success = false;
                        e.ErrorMessage = "File không đúng định dạng!";
                        return;
                    }
                    int timekey = 0;
                    rng = sheet.get_Range("B" + timeRow, "B" + timeRow);
                    string[] arrTime = rng.Value2.ToString().Split(' ');
                    string[] arr = arrTime[arrTime.Length - 1].Split('/');
                    Array.Reverse(arr);
                    string time = arr[0] + arr[1] + arr[2];
                    string slug = "";
                    int i = beginRow - 1;
                    do
                    {
                        i++;
                        rng = sheet.get_Range("B" + i, "B" + i);
                        if (rng.Value2 == null || rng.Value2.ToString() == "")
                        {
                            continue;
                        }
                        fact_costs_revenuesBO obj = new fact_costs_revenuesBO();

                        slug = Global.GlobalMethod.CreateSlug(rng.Value2.ToString());


                        rng = sheet.get_Range("C" + i, "C" + i);

                        string code = rng.Value2 == null ? "" : rng.Value2.ToString();

                        dim_costs_revenuesBO objDim = (dim_costs_revenuesBO)Global.GlobalVariable.HashDimCostRevenue[code];
                        if (objDim != null)
                        {
                            rng = sheet.get_Range("B" + i, "B" + i);
                            objDim.slug = Global.GlobalMethod.CreateSlug(rng.Value2.ToString());
                            dim_costs_revenuesDAO.InsertUpdateDelete(objDim, ProviderAction.Update);
                                //Lib.StringProccess.RemoveUnicode(objDim.name).Replace(" ", "-").Replace("/", "-").Replace(".", "-");
                        }

                        //fact_costs_revenuesDAO.InsertUpdateDelete(obj, ProviderAction.Insert);

                    } while (true);


                }
                catch (System.Exception ex)
                {
                    e.Success = false;
                    e.ErrorMessage = "";
                }
                finally
                {
                    book.Close(false, valueMissing, valueMissing);
                    app.Quit();
                    File.Delete(path);
                }
            }
            catch (Exception ex)
            {
                e.Success = false;
                e.ErrorMessage = "Lỗi hệ thống!\n" + ex.ToString();
            }
        }
    }
}
